Вираз HAVING було додано до SQL, оскільки ключове слово WHERE не можна використовувати з агрегатними функціями.
SQL HAVING використовується для фільтрування значень по групах, створених за допомогою інструкції GROUP BY.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s)
Нижче наведено вибірку з таблиці "Customers" ("Клієнти") бази даних "Northwind":
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 5021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 5023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
У наступному SQL-запиті перераховано кількість клієнтів у кожній країні. Включає лише країни, у яких більше ніж 5 клієнтів:
Run SQLSELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
У наведеному нижче SQL-запиті перераховано кількість клієнтів у кожній країні, відсортованих від більшого до меншого (включає лише країни, де більше ніж 5 клієнтів):
Run SQLSELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC
Давайте поглянемо на вибірку з таблиці "Orders" ("Замовлення"):
| ProductID | OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|---|
| 1 | 10248 | 90 | 5 | 1996-07-04 | 3 |
| 2 | 10249 | 81 | 6 | 1996-07-05 | 1 |
| 3 | 10250 | 34 | 4 | 1996-07-08 | 2 |
| 4 | 10251 | 84 | 3 | 1996-07-08 | 1 |
| 5 | 10252 | 76 | 4 | 1996-07-09 | 2 |
Та вибірку з таблиці "Employees" ("Співробітники"):
| EmployeeID | LastName | FirstName | BirthDate | Photo | Notes |
|---|---|---|---|---|---|
| 1 | Davolio | Nancy | 12/8/1968 | EmpID1.pic | Education includes a BA in psychology from Colorado State University. She also completed (The Art of the Cold Call). Nancy is a member of 'Toastmasters International'. |
| 2 | Fuller | Andrew | 2/19/1952 | EmpID2.pic | Andrew received his BTS commercial and a Ph.D. in international marketing from the University of Dallas. He is fluent in French and Italian and reads German. He joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales. Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association. |
| 3 | Leverling | Janet | 8/30/1963 | EmpID3.pic | Janet has a BS degree in chemistry from Boston College). She has also completed a certificate program in food retailing management. Janet was hired as a sales associate and was promoted to sales representative. |
| 4 | Peacock | Margaret | 9/19/1958 | EmpID4.pic | Margaret holds a BA in English literature from Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned to the London office before returning to her permanent post in Seattle. |
| 5 | Buchanan | Steven | 3/4/1955 | EmpID5.pic | Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree. Upon joining the company as a sales representative, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London, where he was promoted to sales manager. Mr. Buchanan has completed the courses 'Successful Telemarketing' and 'International Sales Management'. He is fluent in French. |
У наведеному нижче SQL-запиті перераховано співробітників, які зареєстрували понад 10 замовлень:
Run SQLSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10
Наступна інструкція SQL відображає дані, якщо співробітники "Davolio" або "Fuller" зареєстрували більше 25 замовлень:
Run SQLSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25